import sys
import pymysql


sys.path.append("/home/jie/.key")
# 本地密码存储文件
from sql_key import password


def run_sql(sql):

    connection = pymysql.connect(
        host="localhost",  # MySQL数据库的主机
        user="root",  # MySQL用户名
        password=password,  # MySQL密码
        database="industry",  # 你要插入数据的数据库
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
    )

    # 插入数据到MySQL
    try:
        with connection.cursor() as cursor:
            res = cursor.execute(
                sql,
            )
            # 提交事务
            connection.commit()

    finally:
        connection.close()
        
    return res


if __name__ == "__main__":
    
    # sql = """
    # SELECT YEAR(grant_publication_date) AS year, COUNT(DISTINCT applicant) AS applicant_count FROM search
    # WHERE grant_publication_date BETWEEN '1985-01-01' AND '2022-12-31' AND applicant = '上海丙嘉精密机械科技有限公司'
    # GROUP BY YEAR(grant_publication_date)
    # ORDER BY year;
    # """.strip()
    
    sql = """
    SELECT COUNT(applicant) AS applicant_count FROM search;
    """.strip()

    res = run_sql(sql=sql)
    print(res)